#ifndef TH_SQL_STATEMENTS_H
#define TH_SQL_STATEMENTS_H

const char * TH_SQL_CREATE_TABLES[] = {
	"Create table S ("
	   "SNO char(4) not null,"
	   "SNAME char(6) not null,"
	   "STATUS tinyint(2) not null,"
	   "CITY char(4))"
	,
	  "create table P("
	  "PNO char(4) not null,"
	  "PNAME char(6) not null,"
	  "COLOR char(2) not null,"
	  "WEIGHT tinyint(2) not null)"
	,
	  "Create table J("
	  "JNO char(4) not null,"
	  "JNAME char(8) not null,"
	  "CITY char(4) not null)"
	,
	  "Create table SPJ("
	  "SNO char(4) not null,"
	  "PNO char(4) not null,"
	  "JNO char(4) not null,"
	  "QTY char(4) not null)"
	,
	NULL
};

const char * TH_SQL_CREATE_TABLE_S = TH_SQL_CREATE_TABLES[0];
const char * TH_SQL_CREATE_TABLE_P = TH_SQL_CREATE_TABLES[1];
const char * TH_SQL_CREATE_TABLE_J = TH_SQL_CREATE_TABLES[2];
const char * TH_SQL_CREATE_TABLE_SPJ = TH_SQL_CREATE_TABLES[3];

const char * TH_SQL_FILL_TABLE_S[] = {
	"insert into S values('S1','精益',20,'天津')",
	"insert into S values('S2','盛锡',10,'北京')",
	"insert into S values('S3','东方红',30,'北京')",
	"insert into S values('S4','丰泰盛',20,'天津')",
	"insert into S values('S5','为民',30,'上海')",
	NULL
};

const char * TH_SQL_FILL_TABLE_P[] = {
	"insert into P values('P1', '螺母','红',12)",
	"insert into P values('P2', '螺栓','绿',17)",
	"insert into P values('P3', '螺丝刀','蓝',14)",
	"insert into P values('P4', '螺丝刀','红',14)",
	"insert into P values('P5', '凸轮','蓝',40)",
	"insert into P values('P6', '齿轮','红',30)",
	NULL
};

const char * TH_SQL_FILL_TABLE_J[] = {
	"insert into J values('J1', '三建','北京')",
	"insert into J values('J2', '一汽','长春')",
	"insert into J values('J3', '弹簧厂','天津')",
	"insert into J values('J4', '造船厂','天津')",
	"insert into J values('J5', '机车厂','唐山')",
	"insert into J values('J6', '无线电厂','唐山')",
	"insert into J values('J7', '半导体厂','南京')",
	NULL
};

const char * TH_SQL_FILL_TABLE_SPJ[] = {
	"insert into SPJ values('S1', 'P1','J1', '200')",
	"insert into SPJ values('S1', 'P1','J3', '100')",
	"insert into SPJ values('S1', 'P1','J4', '700')",
	"insert into SPJ values('S1', 'P2','J2', '100')",
	"insert into SPJ values('S2', 'P3','J1', '400')",
	"insert into SPJ values('S2', 'P3','J2', '200')",
	"insert into SPJ values('S2', 'P3','J4', '500')",
	"insert into SPJ values('S2', 'P3','J5', '400')",
	"insert into SPJ values('S2', 'P5','J1', '400')",
	"insert into SPJ values('S2', 'P5','J2', '100')",
	"insert into SPJ values('S3', 'P1','J1', '200')",
	"insert into SPJ values('S3', 'P3','J1', '200')",
	"insert into SPJ values('S4', 'P5','J1', '100')",
	"insert into SPJ values('S4', 'P6','J3', '300')",
	"insert into SPJ values('S4', 'P6','J4', '200')",
	"insert into SPJ values('S5', 'P2','J4', '100')",
	"insert into SPJ values('S5', 'P3','J1', '200')",
	"insert into SPJ values('S5', 'P6','J2', '200')",
	"insert into SPJ values('S5', 'P6','J4', '500')",
	NULL
};


// 5(1) 找出所有供应商的姓名和所在城市
const char TH_SQL_FIND_PROVIDERS_NAME_AND_CITY[] = "Select SNAME,CITY FROM S";

// (2) 找出所有零件的名称、颜色、重量；
const char TH_SQL_FIND_SPARES_NAME_COLOR_WEIGHT[] = "Select PNAME,COLOR,WEIGHT FROM P";

// (3) 找出使用供应商S1所供应零件的工程号码
const char TH_SQL_SPARES_ID_OF_PROVIDER_S1[] = "Select DISTINCT JNO FROM SPJ WHERE PNO IN (SELECT PNO FROM SPJ WHERE SNO='S1')";

// (4) 找出工程项目J2使用的各种零件的名称及其数量
const char TH_SQL_SPARES_NAME_AND_NUMBER_OF_PROJECT_J2[] = "SELECT PNO,QTY FROM SPJ WHERE JNO='J2'";

// (5)找出上海厂商供应的所有零件号码;
const char TH_SQL_SPARES_ID_OF_SHANGHAI_PROVIDERS[] = "SELECT DISTINCT PNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY='上海')";

// (6)找出使用上海产的零件的工程名称;
const char TH_SQL_PROJECTS_NAME_USING_SPARES_MADE_IN_SHANGHAI[] = "Select JNAME FROM J WHERE JNO IN( SELECT JNO from SPJ where SNO IN (SELECT SNO FROM S WHERE CITY='上海'))";

// (7)找出没有使用天津产的零件的工程号码
const char TH_SQL_PROJECTS_NAME_NOT_USING_SPARES_MADE_IN_TIANJIN[] = "SELECT DISTINCT JNO FROM SPJ WHERE SNO IN (SELECT SNO FROM S WHERE CITY<>'天津')";

// (8)把全部红色零件的颜色改成蓝色;
const char TH_SQL_CHANGE_RED_SPARES_TO_BLUE[] = "UPDATE P SET COLOR='蓝' WHERE COLOR='红'";

// (9)由S5供给J4的零件P6改为由S3供应,请作必要的修改;
const char TH_SQL_CHANGE_SPARES_PROVIDER_1[] = "UPDATE SPJ SET SNO='S3' WHERE SNO='S5'AND JNO='J4'AND PNO='P6'";

// (10)从供应商关系中删除S2的记录,并从供应情况关系中删除相应的记录;
const char TH_SQL_DELETE_S2_FROM_S[] = "DELETE FROM S WHERE SNO='S2'";
const char TH_SQL_DELETE_S@_FROM_PROVISION[] = "DELETE FROM SPJ WHERE SNO='S2'";

// (11)请将(S2,J6,P4,200)插入供应情况关系;
const char TH_SQL_ADD_PROVISION_RECORD_1[] = "Insert into SPJ VALUES('S2', 'P4', 'J6', '200')";

#endif

